package com.dotflat.satistics.repository.crm.impl;

import com.dotflat.satistics.dto.UnitPriceDO;
import com.dotflat.satistics.repository.crm.ContractRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2018/7/18
 * *******************************
 */
@Repository
public class ContractRepositoryImpl implements ContractRepository {

    @Autowired
    private JdbcTemplate crmJdbcTemplate;

    @Override
    public List<Map<String, Object>> findPaidAmountByAreaIdGroupByCreated(Date begin, Date end, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select sum(practicalAmount) as count , paidDate as created from contract " +
                "where paidDate >= ? and paidDate <= ? AND  areaId=? AND payStatus=1 GROUP BY date(paidDate) ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public Double findPaidAmountByAreaId(Date begin, Date end, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select sum(practicalAmount) as count from contract " +
                " where paidDate >= ? and paidDate <= ? AND  areaId=? AND payStatus=1 ", new Object[]{begin, end, areaId}, Double.class);
        return count;
    }

    @Override
    public List<Map<String, Object>> findPaidAmountByAreaIdsInGroupByAreaId(Date begin, Date end, String areaIds) {
        String sql = String.format("select sum(practicalAmount) as count , areaId as areaId from contract " +
                " where paidDate >= ? and paidDate <= ? AND payStatus=1 AND areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end});
        return list;
    }

    @Override
    public List<Map<String, Object>> findMonthPaidAmountByAreaIdsInGroupByAreaId(String month, String areaIds) {
        month += "-01";
        String sql = String.format("select sum(practicalAmount) as count , areaId as areaId from contract " +
                " where  MONTH(paidDate) = MONTH(?) AND payStatus=1 AND  areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{month});
        return list;
    }

    @Override
    public List<Map<String, Object>> findPaidRenewalAmountByAreaIdGroupByCreated(Date begin, Date end, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select sum(practicalAmount) as count , paidDate as created from contract " +
                "where paidDate >= ? and paidDate <= ? AND  areaId=? AND payStatus=1 AND isRenewal=true GROUP BY date(paidDate) ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public Double findPaidRenewalAmountByAreaId(Date begin, Date end, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select sum(practicalAmount) as count from contract " +
                " where paidDate >= ? and paidDate <= ? AND  areaId=? AND payStatus=1 AND isRenewal=true ", new Object[]{begin, end, areaId}, Double.class);
        return count;
    }

    @Override
    public List<Map<String, Object>> findPaidRenewalAmountByAreaIdsInGroupByAreaId(Date begin, Date end, String areaIds) {
        String sql = String.format("select sum(practicalAmount) as count , areaId as areaId from contract " +
                " where paidDate >= ? and paidDate <= ? AND payStatus=1 AND isRenewal=true AND areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end});
        return list;
    }

    @Override
    public List<Map<String, Object>> findMonthPaidRenewalAmountByAreaIdsInGroupByAreaId(String month, String areaIds) {
        month += "-01";
        String sql = String.format("select sum(practicalAmount) as count , areaId as areaId from contract " +
                " where  MONTH(paidDate) = MONTH(?) AND payStatus=1 AND isRenewal=true AND  areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{month});
        return list;
    }

    @Override
    public List<Map<String, Object>> findPaidCountByAreaIdGroupByCreated(Date begin, Date end, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select count(id) as count , paidDate as created from contract " +
                "where paidDate >= ? and paidDate <= ? AND  areaId=? AND payStatus=1 GROUP BY date(paidDate) ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public Double findPaidCountByAreaId(Date begin, Date end, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select count(id) as count from contract " +
                " where paidDate >= ? and paidDate <= ? AND  areaId=? AND payStatus=1 ", new Object[]{begin, end, areaId}, Double.class);
        return count;
    }

    @Override
    public List<Map<String, Object>> findPaidCountByAreaIdsInGroupByAreaId(Date begin, Date end, String areaIds) {
        String sql = String.format("select count(id) as count , areaId as areaId from contract " +
                " where paidDate >= ? and paidDate <= ? AND payStatus=1 AND areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end});
        return list;
    }

    @Override
    public List<Map<String, Object>> findMonthPaidCountByAreaIdsInGroupByAreaId(String month, String areaIds) {
        month += "-01";
        String sql = String.format("select count(id) as count , areaId as areaId from contract " +
                " where  MONTH(paidDate) = MONTH(?) AND payStatus=1 AND  areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{month});
        return list;
    }

    @Override
    public List<Map<String, Object>> findNurseryCountByAreaIdGroupByCreated(Date begin, Date end, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select count(id) as count , created as created from contract " +
                "where created >= ? and created <= ? AND  areaId=? AND (type=1 OR type=3) GROUP BY date(created) ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public Long findNurseryCountByAreaId(Date begin, Date end, String areaId) {
        Long count = crmJdbcTemplate.queryForObject("select count(id) as count from contract " +
                " where created >= ? and created <= ? AND  areaId=?  AND (type=1 OR type=3) ", new Object[]{begin, end, areaId}, Long.class);
        return count;
    }

    @Override
    public List<Map<String, Object>> findNurseryCountByAreaIdsInGroupByAreaId(Date begin, Date end, String areaIds) {
        String sql = String.format("select count(id) as count , areaId as areaId from contract " +
                " where created >= ? and created <= ? AND (type=1 OR type=3) AND areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end});
        return list;
    }

    @Override
    public List<Map<String, Object>> findMonthNurseryCountByAreaIdsInGroupByAreaId(String month, String areaIds) {
        month += "-01";
        String sql = String.format("select count(id) as count , areaId as areaId from contract " +
                " where  MONTH(created) = MONTH(?) AND (type=1 OR type=3) AND  areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{month});
        return list;
    }

    @Override
    public List<Map<String, Object>> findAmountByTypeAndAreaIdGroupByCreated(Date begin, Date end, Integer type, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select sum(totalAmount) as count , created as created from contract " +
                "where created >= ? and created <= ? AND  type=? AND areaId=? GROUP BY date(created) ", new Object[]{begin, end, type, areaId});
        return list;
    }

    @Override
    public Double findAmountByTypeAndAreaId(Date begin, Date end, Integer type, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select sum(totalAmount) as count from contract " +
                " where created >= ? and created <= ? AND  type=? AND  areaId=? ", new Object[]{begin, end, type, areaId}, Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public List<Map<String, Object>> findAmountByTypeAndAreaIdsInGroupByAreaId(Date begin, Date end, Integer type, String areaIds) {
        String sql = String.format("select sum(totalAmount) as count , areaId as areaId, type as type from contract " +
                " where created >= ? and created <= ? AND areaId in (%s) and type = ? GROUP BY areaId  ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end, type});
        return list;
    }

    @Override
    public List<Map<String, Object>> findMonthAmountByTypeAndAreaIdsInGroupByAreaId(String month, Integer type, String areaIds) {
        month += "-01";
        String sql = String.format("select sum(totalAmount) as count , areaId as areaId from contract " +
                " where  MONTH(created) = MONTH(?) AND  type=? AND  areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{month, type});
        return list;
    }

    @Override
    public List<Map<String, Object>> findCountByAreaIdGroupByCreated(Date begin, Date end, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select count(id) as count , created as created from contract " +
                "where created >= ? and created <= ? AND areaId=? GROUP BY date(created) ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public Long findCountByAreaId(Date begin, Date end, String areaId) {
        Long count = crmJdbcTemplate.queryForObject("select count(id) as count from contract " +
                " where created >= ? and created <= ? AND  areaId=? ", new Object[]{begin, end, areaId}, Long.class);
        return count;
    }

    @Override
    public List<Map<String, Object>> findCountByAreaIdsInGroupByAreaId(Date begin, Date end, String areaIds) {
        String sql = String.format("select count(id) as count , areaId as areaId from contract " +
                " where created >= ? and created <= ? AND areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end});
        return list;
    }

    @Override
    public List<Map<String, Object>> findMonthCountByAreaIdsInGroupByAreaId(String month, String areaIds) {
        month += "-01";
        String sql = String.format("select count(id) as count , areaId as areaId from contract " +
                " where  MONTH(created) = MONTH(?)  AND  areaId in (%s) GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{month});
        return list;
    }

    @Override
    public List<Map<String, Object>> findIntroduceCountByAreaIdGroupByCreated(Date begin, Date end, String areaId,String channelId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select count(co.id) as count , co.created as created from contract co LEFT JOIN clientele cl on co.clienteleId = cl.id " +
                "where co.created >= ? and co.created <= ? AND co.areaId=? AND cl.channelId = ? GROUP BY date(co.created) ", new Object[]{begin, end, areaId,channelId});
        return list;
    }

    @Override
    public Long findIntroduceCountByAreaId(Date begin, Date end, String areaId,String channelId) {
        Long count = crmJdbcTemplate.queryForObject("select count(co.id) as count from contract co LEFT JOIN clientele cl on co.clienteleId = cl.id " +
                " where co.created >= ? and co.created <= ? AND co.areaId=? AND cl.channelId = ? ", new Object[]{begin, end, areaId,channelId}, Long.class);
        return count;
    }

    @Override
    public List<Map<String, Object>> findIntroduceCountByAreaIdsInGroupByAreaId(Date begin, Date end, String areaIds,String channelId) {
        String sql = String.format("select count(co.id) as count , co.areaId as areaId from contract co LEFT JOIN clientele cl on co.clienteleId = cl.id " +
                "  where co.created >= ? and co.created <= ?  AND cl.channelId = '%s' AND co.areaId in (%s) GROUP BY co.areaId ",channelId,areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end});
        return list;
    }

    @Override
    public List<Map<String, Object>> findMonthIntroduceCountByAreaIdsInGroupByAreaId(String month, String areaIds,String channelId) {
        month += "-01";
        String sql = String.format("select count(co.id) as count , co.areaId as areaId from contract co LEFT JOIN clientele cl on co.clienteleId = cl.id " +
                " where  MONTH(co.created) = MONTH(?)  AND  co.areaId in (%s) AND cl.channelId = '%s' GROUP BY co.areaId ", areaIds,channelId);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{month});
        return list;
    }

    @Override
    public List<Map<String, Object>> findPaidAmountByAreaIdAndProductTypeGroupByPaidDate(Date begin, Date end, String areaId, Integer propertyType) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select sum(cp.practicalTotalAmount) as count , c.paidDate as created, cp.productType as productType from contract c left join contract_product cp on c.id = cp.contractId " +
                "where c.paidDate >= ? and c.paidDate <= ? AND c.payStatus = 1 AND c.areaId=? and productType=? GROUP BY date(c.paidDate)", new Object[]{begin, end, areaId, propertyType});
        return list;
    }

    @Override
    public List<Map<String, Object>> findPaidAmountByAreaIdAndProductType(Date begin, Date end, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select sum(cp.practicalTotalAmount) as count , cp.productType as productType from contract c left join contract_product cp on c.id = cp.contractId " +
                "where c.paidDate >= ? and c.paidDate <= ? AND c.payStatus = 1 AND c.areaId=? GROUP BY cp.productType ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public List<Map<String, Object>> findCountByTypeAndAreaIdGroupByCreated(Date begin, Date end, String areaId, Integer type) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select count(id) as count , created as created from contract " +
                "where created >= ? and created <= ? AND  type=? AND areaId=? GROUP BY date(created) ", new Object[]{begin, end, type, areaId});
        return list;
    }

    @Override
    public List<Map<String, Object>> findCountByAreaIdGroupByType(Date begin, Date end, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select count(id) as count , type as type from contract " +
                "where created >= ? and created <= ? AND areaId=? GROUP BY type ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public List<Map<String, Object>> findPaidAmountByAreaIdGroupByMonth(Date begin, Date end, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select sum(practicalAmount) as count , paidDate as created from contract " +
                "where paidDate >= ? and paidDate <= ? AND  areaId=? AND payStatus=1 GROUP BY MONTH (paidDate) ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public List<Map<String, Object>> findPaidAmountByAreaIdGroupByCreatedAndAreaId(Date begin, Date end, String areaIds) {
        String sql = String.format("select sum(practicalAmount) as count , paidDate as created, areaId as areaId from contract " +
                "where paidDate >= ? and paidDate <= ? AND payStatus=1 AND  areaId in (%s) GROUP BY date(paidDate), areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end});
        return list;
    }

    @Override
    public List<Map<String, Object>> findUnitPriceByAreaIdGroupByCreated(Date begin, Date end, String areaId) {
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList("select sum(practicalAmount) as totalAmount , count(id) as count, paidDate as created from contract " +
                "where paidDate >= ? and paidDate <= ? AND payStatus=1 AND areaId = ? GROUP BY date(paidDate) ", new Object[]{begin, end, areaId});
        return list;
    }

    @Override
    public List<Map<String, Object>> findUnitPriceByAreaIdInGroupByAreaId(Date begin, Date end, String areaIds) {
        String sql = String.format("select areaId as areaId, sum(practicalAmount) as totalAmount, count(id) as count, date(paidDate) as created from contract " +
                "where  paidDate >= ? and  paidDate <= ? AND  areaId IN (%s)  AND payStatus=1 GROUP BY areaId ", areaIds);
        List<Map<String, Object>> list = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end});
        return list;
    }

    @Override
    public Double findActualByAreaIdAndMonthAndPayStatus(Date begin, Date end, String areaId, int status) {
        Double sum = crmJdbcTemplate.queryForObject("select sum(practicalAmount) as totalAmount from contract where created >= ? and created <= ? and areaId = ? and status != ? ", new Object[]{begin, end, areaId, status}, Double.class);
        return Objects.isNull(sum) ? 0d : sum;
    }

    @Override
    public UnitPriceDO findUnitPriceByAreaId(Date begin, Date end, String areaId) {
        List<UnitPriceDO> dataList = crmJdbcTemplate.query("select sum(practicalAmount) as totalAmount, count(id) as count, date(paidDate) as created from contract " +
                "where  date(paidDate) >= date(?) and  date(paidDate) <= date(?) AND  areaId =?  AND payStatus=1", new Object[]{begin, end, areaId}, new BeanPropertyRowMapper<UnitPriceDO>(UnitPriceDO.class));
        return dataList.size() == 0 ? null : dataList.get(0);
    }

    @Override
    public UnitPriceDO findUnitPriceByAreaIdIn(Date begin, Date end, String areaIds) {
        String sql = String.format("select sum(practicalAmount) as totalAmount, count(id) as count, date(paidDate) as created from contract " +
                "where  date(paidDate) >= date(?) and  date(paidDate) <= date(?) AND  areaId IN (%s)  AND payStatus=1 ", areaIds);
        List<UnitPriceDO> dataList = crmJdbcTemplate.query(sql, new Object[]{begin, end}, new BeanPropertyRowMapper<UnitPriceDO>(UnitPriceDO.class));
        return dataList.size() == 0 ? null : dataList.get(0);
    }

    @Override
    public Double findCountByTypeAndArea(Date begin, Date end, int type, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select sum(cp.count) as count from contract as con,contract_product as cp " +
                " where con.id=cp.contractId and con.created >= ? and con.created <= ? AND  con.type=? AND  con.areaId=? and cp.productType= ? ", new Object[]{begin, end, type, areaId, type}, Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public Double findNurseryCountByStatusAndPayStatusAndAreaIdAndIsrewal(Date begin, Date end, int status, int payStatus, int isRenewal, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select count(id) from contract as con where created >=? and created <=? and status !=? and payStatus =? and isRenewal = ? and areaId =? and type IN(1,3)",new Object[]{begin,end,status,payStatus,isRenewal,areaId},Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public Double findNurseryCountByStatusAndPayStatusAndAreaId(Date begin, Date end, int status, int payStatus, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select count(id) from contract as con where created >=? and created <=? and status !=? and payStatus =? and areaId =? and type IN(1,3)",new Object[]{begin,end,status,payStatus,areaId},Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public Long findCountByChannelAndTypeAndStatusAndPayStatus(Date begin, Date end, String areaId, String channelId, int type, int contractStatus, int contractPayStatus) {
        String sql="SELECT count(DISTINCT(con.clienteleId)) from leads as le ,clientele as cl ,contract as con WHERE le.id=cl.leadsId and cl.id=con.clienteleId and le.created >=? and le.created <=? and le.areaId =? and le.channelId=? and le.category=? and con.status!=? and con.payStatus=? ;";
        Long count = crmJdbcTemplate.queryForObject(sql,new Object[]{begin,end,areaId,channelId,type,contractStatus,contractPayStatus},Long.class);
        return count;
    }

}
